/* To close the Lot, if the lot is running state in previous location */
--EXEC sp_close_lots_by_previous_process_step_id 1071, '4532507', 4, 'TEST'

Alter proc sp_close_lots_by_previous_process_step_id (

	@PROCESS_STEP_ID	INT,
	@LOT_NUMBER 		VARCHAR(50),
	@LOCATION_ID 		INT,
	@PS_TYPE		VARCHAR(50)
)

AS 
BEGIN

	DECLARE @PREVIOUS_PROCESSSTEP_ID 	INT
	DECLARE @PREVIOUS_PROCESSSTEP_TYPE 	VARCHAR(50)
	DECLARE @PREVIOUS_PROCESSSTEP_TYPE_NAME VARCHAR(50)
	DECLARE @LOT_ID 			INT
	DECLARE @TEMPLOCATION_ID 		INT
	DECLARE @RECORD_COUNT 			INT
	DECLARE @TEMPCURSOR 			CURSOR
	DECLARE @LOCATIONCURSOR 		CURSOR

	SET @TEMPCURSOR = CURSOR FOR
	SELECT PROCESSSTEP_ID, PS_TYPE, PROCESS_TYPE_NAME FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID (@PROCESS_STEP_ID)
	OPEN @TEMPCURSOR
	FETCH NEXT FROM @TEMPCURSOR INTO @PREVIOUS_PROCESSSTEP_ID, @PREVIOUS_PROCESSSTEP_TYPE, @PREVIOUS_PROCESSSTEP_TYPE_NAME
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @LOCATIONCURSOR = CURSOR FOR
		SELECT DISTINCT LOCATION_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PREVIOUS_PROCESSSTEP_ID

		OPEN @LOCATIONCURSOR
		FETCH NEXT FROM @LOCATIONCURSOR INTO @TEMPLOCATION_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @LOT_ID = 0
 			SELECT TOP 1 @LOT_ID = LOT_ID FROM LOT_TABLE WHERE LOT_NUMBER = @LOT_NUMBER AND LOCATION_ID = @TEMPLOCATION_ID AND PROCESSSTEP_ID = @PREVIOUS_PROCESSSTEP_ID	

 			IF @LOT_ID <> 0 AND @PREVIOUS_PROCESSSTEP_TYPE_NAME <> @PS_TYPE
 			BEGIN
				PRINT @LOT_ID
 				/* to close the lot in previous location*/
				UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
 			END
			--IF  @PS_TYPE = 'TRANSIT'
 			--BEGIN
 				/* to close the lot in previous location*/
				--UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
 			--END

		SET @TEMPLOCATION_ID = NULL 
		FETCH NEXT FROM @LOCATIONCURSOR INTO @TEMPLOCATION_ID
		END
		
		CLOSE @LOCATIONCURSOR
		DEALLOCATE @LOCATIONCURSOR

	SET @PREVIOUS_PROCESSSTEP_ID 	= NULL 
	SET @PREVIOUS_PROCESSSTEP_TYPE 	= NULL 
	SET @PREVIOUS_PROCESSSTEP_TYPE_NAME= NULL

	FETCH NEXT FROM @TEMPCURSOR INTO @PREVIOUS_PROCESSSTEP_ID, @PREVIOUS_PROCESSSTEP_TYPE, @PREVIOUS_PROCESSSTEP_TYPE_NAME
	END	
	
	CLOSE @TEMPCURSOR
	DEALLOCATE @TEMPCURSOR

END